--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Node / ReticulumProjects / MeshChatX.git / files / meshchatx / src / backend / database / announces.py
Displaying Raw • Download
meshchatx/src/backend/database/announces.py 2397eeff20df21079f1567dbf3ed2a7cd06a58ad (2397eeff) Text, 7.76 KB
T8b949e# SPDX-License-Identifier: 0BSD
Tff7b72from T7ee787datetime Tff7b72import Te6edf3UTCTb4b4b4, Te6edf3datetime
Tff7b72from T7ee787.T7ee787provider Tff7b72import Te6edf3DatabaseProvider
Tff7b72class T56d364AnnounceDAOTb4b4b4:
Tff7b72def Tff7b72__init__Tb4b4b4(Tff7b72selfTb4b4b4, Te6edf3providerTb4b4b4: Te6edf3DatabaseProviderTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffprovider Tff7b72= Te6edf3provider
Tff7b72def Td2a8ffupsert_announceTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3dataTb4b4b4)Tb4b4b4:
Tff7b72if Tff7b72not Tffa657isinstanceTb4b4b4(Te6edf3dataTb4b4b4, Tffa657dictTb4b4b4)Tb4b4b4:
Te6edf3data Tff7b72= Tffa657dictTb4b4b4(Te6edf3dataTb4b4b4)
Te6edf3fields Tff7b72= Tb4b4b4[
Ta5d6ff"Ta5d6ffdestination_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffaspectTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffidentity_hashTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffidentity_public_keyTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffapp_dataTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffrssiTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffsnrTa5d6ff"Tb4b4b4,
Ta5d6ff"Ta5d6ffqualityTa5d6ff"Tb4b4b4,
Tb4b4b4]
Te6edf3columns Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Te6edf3fieldsTb4b4b4)
Te6edf3placeholders Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Tb4b4b4[Ta5d6ff"Ta5d6ff?Ta5d6ff"Tb4b4b4] Tff7b72* Tffa657lenTb4b4b4(Te6edf3fieldsTb4b4b4)Tb4b4b4)
Te6edf3update_parts Tff7b72= Tb4b4b4[Tb4b4b4]
Tff7b72for Te6edf3f Tff7b72in Te6edf3fieldsTb4b4b4:
Tff7b72if Te6edf3f Tff7b72== Ta5d6ff"Ta5d6ffdestination_hashTa5d6ff"Tb4b4b4:
Tff7b72continue
Tff7b72if Te6edf3f Tff7b72== Ta5d6ff"Ta5d6ffapp_dataTa5d6ff"Tb4b4b4:
Te6edf3update_partsTff7b72.Td2a8ffappendTb4b4b4(
Ta5d6ff"Ta5d6ffapp_data = COALESCE(EXCLUDED.app_data, announces.app_data)Ta5d6ff"Tb4b4b4,
Tb4b4b4)
Tff7b72elseTb4b4b4:
Te6edf3update_partsTff7b72.Td2a8ffappendTb4b4b4(Ta5d6fffTa5d6ff"Tffd700{Te6edf3fTffd700}Ta5d6ff = EXCLUDED.Tffd700{Te6edf3fTffd700}Ta5d6ff"Tb4b4b4)
Te6edf3update_set Tff7b72= Ta5d6ff"Ta5d6ff, Ta5d6ff"Tff7b72.Td2a8ffjoinTb4b4b4(Te6edf3update_partsTb4b4b4)
Te6edf3query Tff7b72= Tb4b4b4(
Ta5d6fffTa5d6ff"Ta5d6ffINSERT INTO announces (Tffd700{Te6edf3columnsTffd700}Ta5d6ff, created_at, updated_at) VALUES (Tffd700{Te6edf3placeholdersTffd700}Ta5d6ff, ?, ?) Ta5d6ff"
Ta5d6fffTa5d6ff"Ta5d6ffON CONFLICT(destination_hash) DO UPDATE SET Tffd700{Te6edf3update_setTffd700}Ta5d6ff, updated_at = EXCLUDED.updated_atTa5d6ff"
Tb4b4b4)
Te6edf3params Tff7b72= Tb4b4b4[Te6edf3dataTff7b72.Td2a8ffgetTb4b4b4(Te6edf3fTb4b4b4) Tff7b72for Te6edf3f Tff7b72in Te6edf3fieldsTb4b4b4]
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3nowTb4b4b4)
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3nowTb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3paramsTb4b4b4)
Tff7b72def Td2a8fftrim_announces_for_aspectTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3aspectTb4b4b4, Te6edf3max_rowsTb4b4b4)Tb4b4b4:
T8b949e"""Delete oldest rows for this aspect until at most max_rows remain.
Announces that correspond to a favourited destination or to a saved
contact are considered protected and are never deleted by this trim,
even if the total count exceeds ``max_rows``. This prevents purging
of announces (and the path/identity context they provide) for
favourited NomadNet nodes and for messaging contacts when storage
limits are enforced.
"""
Tff7b72if Te6edf3max_rows Tff7b72< T79c0ff1 Tff7b72or Tff7b72not Te6edf3aspectTb4b4b4:
Tff7b72return
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT COUNT(*) AS c FROM announces WHERE aspect = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3aspectTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Te6edf3count Tff7b72= Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcTa5d6ff"Tb4b4b4] Tff7b72if Te6edf3row Tff7b72else T79c0ff0
Te6edf3excess Tff7b72= Te6edf3count Tff7b72- Te6edf3max_rows
Tff7b72if Te6edf3excess Tff7b72<Tff7b72= T79c0ff0Tb4b4b4:
Tff7b72return
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
DELETE FROM announces WHERE id IN (
SELECT a.id FROM announces a
WHERE a.aspect = ?
AND NOT EXISTS (
SELECT 1 FROM favourite_destinations f
WHERE f.destination_hash = a.destination_hash
)
AND NOT EXISTS (
SELECT 1 FROM contacts c
WHERE c.remote_identity_hash = a.identity_hash
OR c.lxmf_address = a.destination_hash
OR c.lxst_address = a.destination_hash
)
ORDER BY a.updated_at ASC, a.id ASC
LIMIT ?
)
"""Tb4b4b4,
Tb4b4b4(Te6edf3aspectTb4b4b4, Te6edf3excessTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_announcesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3aspectTff7b72=Tff7b72NoneTb4b4b4)Tb4b4b4:
Tff7b72if Te6edf3aspectTb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM announces WHERE aspect = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3aspectTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Ta5d6ff"Ta5d6ffSELECT * FROM announcesTa5d6ff"Tb4b4b4)
Tff7b72def Td2a8ffget_announce_by_hashTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM announces WHERE destination_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_announce_count_by_aspectTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3aspectTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT COUNT(*) as count FROM announces WHERE aspect = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3aspectTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffcountTa5d6ff"Tb4b4b4] Tff7b72if Te6edf3row Tff7b72else T79c0ff0
Tff7b72def Td2a8ffdelete_all_announcesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3aspectTff7b72=Tff7b72NoneTb4b4b4)Tb4b4b4:
Tff7b72if Te6edf3aspectTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM announces WHERE aspect = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3aspectTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72elseTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"Ta5d6ffDELETE FROM announcesTa5d6ff"Tb4b4b4)
Tff7b72def Td2a8ffget_filtered_announcesTb4b4b4(
Tff7b72selfTb4b4b4,
Te6edf3aspectTff7b72=Tff7b72NoneTb4b4b4,
Te6edf3search_termTff7b72=Tff7b72NoneTb4b4b4,
Te6edf3identity_hashTff7b72=Tff7b72NoneTb4b4b4,
Te6edf3destination_hashTff7b72=Tff7b72NoneTb4b4b4,
Te6edf3limitTff7b72=T79c0ff2500Tb4b4b4,
Te6edf3offsetTff7b72=T79c0ff0Tb4b4b4,
Tb4b4b4)Tb4b4b4:
Te6edf3query Tff7b72= Ta5d6ff"Ta5d6ffSELECT * FROM announces WHERE 1=1Ta5d6ff"
Te6edf3params Tff7b72= Tb4b4b4[Tb4b4b4]
Tff7b72if Te6edf3aspectTb4b4b4:
Te6edf3query Tff7b72+Tff7b72= Ta5d6ff"Ta5d6ff AND aspect = ?Ta5d6ff"
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3aspectTb4b4b4)
Tff7b72if Te6edf3identity_hashTb4b4b4:
Te6edf3query Tff7b72+Tff7b72= Ta5d6ff"Ta5d6ff AND identity_hash = ?Ta5d6ff"
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3identity_hashTb4b4b4)
Tff7b72if Te6edf3destination_hashTb4b4b4:
Te6edf3query Tff7b72+Tff7b72= Ta5d6ff"Ta5d6ff AND destination_hash = ?Ta5d6ff"
Te6edf3paramsTff7b72.Td2a8ffappendTb4b4b4(Te6edf3destination_hashTb4b4b4)
Tff7b72if Te6edf3search_termTb4b4b4:
Te6edf3query Tff7b72+Tff7b72= Ta5d6ff"Ta5d6ff AND (destination_hash LIKE ? OR identity_hash LIKE ?)Ta5d6ff"
Te6edf3like_term Tff7b72= Ta5d6fffTa5d6ff"Ta5d6ff%Tffd700{Te6edf3search_termTffd700}Ta5d6ff%Ta5d6ff"
Te6edf3paramsTff7b72.Td2a8ffextendTb4b4b4(Tb4b4b4[Te6edf3like_termTb4b4b4, Te6edf3like_termTb4b4b4]Tb4b4b4)
Te6edf3query Tff7b72+Tff7b72= Ta5d6ff"Ta5d6ff ORDER BY updated_at DESCTa5d6ff"
Tff7b72if Te6edf3limitTb4b4b4:
Te6edf3query Tff7b72+Tff7b72= Ta5d6ff"Ta5d6ff LIMIT ? OFFSET ?Ta5d6ff"
Te6edf3paramsTff7b72.Td2a8ffextendTb4b4b4(Tb4b4b4[Te6edf3limitTb4b4b4, Te6edf3offsetTb4b4b4]Tb4b4b4)
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Te6edf3queryTb4b4b4, Te6edf3paramsTb4b4b4)
T8b949e# Custom Display Names
Tff7b72def Td2a8ffupsert_custom_display_nameTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3display_nameTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO custom_destination_display_names (destination_hash, display_name, created_at, updated_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(destination_hash) DO UPDATE SET display_name = EXCLUDED.display_name, updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3display_nameTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_custom_display_nameTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Te6edf3row Tff7b72= Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT display_name FROM custom_destination_display_names WHERE destination_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Te6edf3rowTb4b4b4[Ta5d6ff"Ta5d6ffdisplay_nameTa5d6ff"Tb4b4b4] Tff7b72if Te6edf3row Tff7b72else Tff7b72None
Tff7b72def Td2a8ffdelete_custom_display_nameTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM custom_destination_display_names WHERE destination_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
T8b949e# Favourites
Tff7b72def Td2a8ffupsert_favouriteTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4, Te6edf3display_nameTb4b4b4, Te6edf3aspectTb4b4b4)Tb4b4b4:
Te6edf3now Tff7b72= Te6edf3datetimeTff7b72.Td2a8ffnowTb4b4b4(Te6edf3UTCTb4b4b4)
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
T8b949e"""
INSERT INTO favourite_destinations (destination_hash, display_name, aspect, created_at, updated_at)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(destination_hash) DO UPDATE SET display_name = EXCLUDED.display_name, aspect = EXCLUDED.aspect, updated_at = EXCLUDED.updated_at
"""Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4, Te6edf3display_nameTb4b4b4, Te6edf3aspectTb4b4b4, Te6edf3nowTb4b4b4, Te6edf3nowTb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_favourite_by_destination_hashTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchoneTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM favourite_destinations WHERE destination_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffget_favouritesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3aspectTff7b72=Tff7b72NoneTb4b4b4)Tb4b4b4:
Tff7b72if Te6edf3aspectTb4b4b4:
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(
Ta5d6ff"Ta5d6ffSELECT * FROM favourite_destinations WHERE aspect = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3aspectTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72return Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8fffetchallTb4b4b4(Ta5d6ff"Ta5d6ffSELECT * FROM favourite_destinationsTa5d6ff"Tb4b4b4)
Tff7b72def Td2a8ffdelete_favouriteTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3destination_hashTb4b4b4)Tb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM favourite_destinations WHERE destination_hash = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3destination_hashTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72def Td2a8ffdelete_all_favouritesTb4b4b4(Tff7b72selfTb4b4b4, Te6edf3aspectTff7b72=Tff7b72NoneTb4b4b4)Tb4b4b4:
Tff7b72if Te6edf3aspectTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(
Ta5d6ff"Ta5d6ffDELETE FROM favourite_destinations WHERE aspect = ?Ta5d6ff"Tb4b4b4,
Tb4b4b4(Te6edf3aspectTb4b4b4,Tb4b4b4)Tb4b4b4,
Tb4b4b4)
Tff7b72elseTb4b4b4:
Tff7b72selfTff7b72.Td2a8ffproviderTff7b72.Td2a8ffexecuteTb4b4b4(Ta5d6ff"Ta5d6ffDELETE FROM favourite_destinationsTa5d6ff"Tb4b4b4)
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────